//加载express框架
let express = require("express");
var router = express.Router();
//配置模板引擎
let ejs = require("ejs");

//加载mysql数据库
let mysql = require("mysql");
//配置mysql数据库
let config = {
  host: "localhost", //数据库连接服务
  port: 3306, //数据库端口
  user: "root", //数据库用户名
  password: "root", //数据库密码
  database: "student", //选择要操作的库房
}

//连接数据库
let connection = mysql.createConnection(config);

//判断是否连接成功
connection.connect((err) => {
  if (err) {
    console.log("连接数据库失败")
  } else {
    console.log("数据库连接成功")
  }
})



//渲染首页页面
router.get("/index", (req, res) => {
  //查询数据
  let sql = `select * from stu_score`;
  connection.query(sql, (err, doc) => {
    if (err) {
      console.log("查询失败")
    } else {
      //console.log(doc);
      res.render("index.html", {
        doc: doc
      });
    }
  })
})


//渲染添加页面
router.get("/add", (req, res) => {
  res.render("add.html")
})

//处理表单数据
router.post("/add", (req, res) => {
  //获取表单数据
  //console.log(req.body)
  let {
    name,
    age,
    sex
  } = req.body;
  let sql = `insert into stu_score (name,age,sex) value ("${name}",${age},"${sex}")`;
  connection.query(sql, (err) => {
    if (err) {
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "添加失败",
        href: "/add"
      })
    } else {
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "添加成功",
        href: "/index"
      })
    }
  })
})


//删除数据
router.get("/del", (req, res) => {
  //更加id删除 
  //获取当前点击的id值
  let id = req.query.id;
  //console.log(id);
  let sql = `delete from stu_score where id=${id}`;
  connection.query(sql, (err) => {
    if (err) {
      //res.send("删除失败")
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "删除失败",
        href: "/index"
      })
    } else {
      //res.send("删除成功")
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "删除成功",
        href: "/index"
      })
    }
  })
})


//修改数据  查询对应的数据
router.get("/update", (req, res) => {

  //查询数据
  let id = req.query.id;
  let sql = `select * from stu_score where id=${id}`;
  connection.query(sql, (err, doc) => {
    if (err) {
      console.log("查询数据失败")
    } else {
      //         console.log(doc);

      res.render("edit.html",{result:doc});
    }

  })


})

//处理表单
router.post("/update", (req, res) => {
  //获取表单提交的数据
  let {
    name,
    age,
    sex,
    id
  } = req.body;
  //console.log(name, age,sex, id);
  let sql = `update stu_score set name="${name}",age=${age},sex="${sex}" where id=${id}`
  //console.log(sql);
  connection.query(sql, (err) => {
    if (err) {
      //res.send("删除失败")
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "修改失败",
        href: "/update"
      })
    } else {
      //res.send("删除成功")
      res.render("wait.html", {
        wait: 2, //等待时间
        content: "修改成功",
        href: "/index"
      })
    }
  })
})

module.exports = router;